package com.china08.yxyapi.service.continuedu.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.china08.yxyapi.service.continuedu.YxyContinueduRenWuService;

@Service
public class YxyContinueduRenWuServiceImpl implements YxyContinueduRenWuService {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public Map<String, Object> lessonList(List<String> lessonIds, String schoolId, String orderBy1, String orderBy2, String keyword, Integer pageNum) {

        List<Object> paramList = new ArrayList<Object>();
        paramList.add(schoolId);
        paramList.add(1);
        paramList.add(1);

        StringBuffer sb1 = new StringBuffer();
        sb1.append(" select                                         ");
        sb1.append("   count(id)                                    ");
        sb1.append(" from yxy_continuedu.yxy_continuedu_lesson      ");
        sb1.append(" where school_id = ?                            ");
        sb1.append("     and status = ?                             ");
        sb1.append("     and is_visible = ?                         ");
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append(" and name like ?                            ");
            paramList.add(keyword);
        }
        // 去除已经选中的课程
        for (String lessonId : lessonIds) {
            sb1.append(" and id != ?                                ");
            paramList.add(lessonId);
        }
        Integer allPage = jdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb2 = new StringBuffer();
        sb2.append(" select                                         ");
        sb2.append("   id,                                          ");
        sb2.append("   name,                                        ");
        sb2.append("   summary,                                     ");
        sb2.append("   cover,                                       ");
        sb2.append("   count,                                       ");
        sb2.append("   duration,                                    ");
        sb2.append("   pv,                                          ");
        sb2.append("   vv                                           ");
        sb2.append(" from yxy_continuedu.yxy_continuedu_lesson      ");
        sb2.append(" where school_id = ?                            ");
        sb2.append("     and status = ?                             ");
        sb2.append("     and is_visible = ?                         ");
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append(" and name like ?                            ");
        }
        // 去除已经选中的课程
        for (int i = 0; i < lessonIds.size(); i++) {
            sb2.append(" and id != ?                                ");
        }
        if (StringUtils.isNotBlank(orderBy1)) {
            sb2.append(" order by up_date desc                      ");
        }
        if (StringUtils.isNotBlank(orderBy2)) {
            sb2.append(" order by pv desc                           ");
        }
        if (StringUtils.isBlank(orderBy1) && StringUtils.isBlank(orderBy2)) {
            sb2.append(" order by up_date desc                      ");
        }
        sb2.append(" limit ?,?                                      ");
        paramList.add((pageNum - 1) * 12);
        paramList.add(12);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb2.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("allPage", allPage);
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> renwuList(String schoolId, String keyword, Integer status, Integer pageNum) {
        List<Object> paramList = new ArrayList<>();
        paramList.add(schoolId);

        StringBuffer sb = new StringBuffer();
        sb.append("  select                                     ");
        sb.append("    count(id)                                ");
        sb.append("  from yxy_continuedu.yxy_continuedu_task    ");
        sb.append("  where school_id = ?                        ");
        if (status != 2) {
            sb.append(" and status = ?                          ");
            paramList.add(status);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb.append(" and name like ?                         ");
            paramList.add(keyword);
        }
        Integer allPage = jdbcTemplate.queryForObject(sb.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb1 = new StringBuffer();
        sb1.append("   select                                       ");
        sb1.append("     id,                                        ");
        sb1.append("     name,                                      ");
        sb1.append("     score,                                     ");
        sb1.append("     begin_date,                                ");
        sb1.append("     end_date,                                  ");
        sb1.append("     status                                     ");
        sb1.append("   from yxy_continuedu.yxy_continuedu_task      ");
        sb1.append("   where school_id = ?                          ");
        if (status != 2) {
            sb1.append(" and status = ?                             ");
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append(" and name like ?                            ");
        }
        sb1.append("  order by begin_date desc, end_date desc, id asc ");
        sb1.append(" limit ?,?                                        ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb1.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("allPage", allPage);
        map.put("list", list);
        return map;
    }

    @Override
    public Integer findTaskLessonDuration(String taskId) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select                                                  ");
        sb.append("   sum(t2.duration)    duration                          ");
        sb.append(" from yxy_continuedu.yxy_continuedu_task_lesson t1       ");
        sb.append("   left join yxy_continuedu.yxy_continuedu_lesson t2     ");
        sb.append("     on (t1.lesson_id = t2.id)                           ");
        sb.append(" where task_id = ?                                       ");
        // sb.append(" group by lesson_id ");
        try {
            return jdbcTemplate.queryForObject(sb.toString(), new Object[] { taskId }, Integer.class);
        } catch (Exception e) {
            return 0;
        }
    }

    @Override
    public Map<String, Object> renwuList(String schoolId, String keyword, String orderBy, String orderByStatus, Integer pageNum) {

        List<Object> paramList = new ArrayList<>();
        paramList.add(schoolId);

        StringBuffer sb1 = new StringBuffer();
        sb1.append(" select                                                 ");
        sb1.append("   count(id)                                            ");
        sb1.append(" from yxy_continuedu.yxy_continuedu_task                ");
        sb1.append(" where school_id = ?                                    ");
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append(" and name like ?                                    ");
            paramList.add(keyword);
        }
        Integer allPage = jdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb2 = new StringBuffer();
        sb2.append("   select                                       ");
        sb2.append("     id,                                        ");
        sb2.append("     name,                                      ");
        sb2.append("     begin_date,                                 ");
        sb2.append("     end_date,                                   ");
        sb2.append("     apply_count,                                ");
        sb2.append("     complete_count,                             ");
        sb2.append("     complete_rate,                              ");
        sb2.append("     status                                     ");
        sb2.append("   from yxy_continuedu.yxy_continuedu_task      ");
        sb2.append("   where school_id = ?                          ");
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append(" and name like ?                            ");
        }
        if (StringUtils.isNotBlank(orderBy)) {
            if ("applyCount".equals(orderBy) && "up".equals(orderByStatus)) {
                sb2.append(" order by apply_count asc               ");
            }
            if ("applyCount".equals(orderBy) && "down".equals(orderByStatus)) {
                sb2.append(" order by apply_count desc              ");
            }
            if ("completeCount".equals(orderBy) && "up".equals(orderByStatus)) {
                sb2.append(" order by complete_count asc            ");
            }
            if ("completeCount".equals(orderBy) && "down".equals(orderByStatus)) {
                sb2.append(" order by complete_count desc           ");
            }
            if ("completeRate".equals(orderBy) && "up".equals(orderByStatus)) {
                sb2.append(" order by complete_rate asc             ");
            }
            if ("completeRate".equals(orderBy) && "down".equals(orderByStatus)) {
                sb2.append(" order by complete_rate desc            ");
            }
        } else {
            sb2.append(" order by begin_date desc, end_date desc, id asc    ");
        }
        sb2.append(" limit ?,?                                      ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb2.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("allPage", allPage);
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> renwuDetailList(String taskId, String areaIdProvince, String areaIdCity, String areaIdDistrict, Integer bureauLevel, String keyword, Integer pageNum) {
        StringBuffer sb1 = new StringBuffer();
        List<Object> paramList = new ArrayList<>();
        paramList.add(taskId);
        sb1.append(" select count(id)                                                       ");
        sb1.append(" from yxy_continuedu.yxy_continuedu_statistics_school_task              ");
        sb1.append(" where task_id = ?                                                      ");
        if (StringUtils.isNotBlank(areaIdProvince)) {
            sb1.append(" and area_code_province = ?                                         ");
            paramList.add(areaIdProvince);
        }
        if (StringUtils.isNotBlank(areaIdCity)) {
            sb1.append(" and area_code_city = ?                                             ");
            paramList.add(areaIdCity);
        }
        if (StringUtils.isNotBlank(areaIdDistrict)) {
            sb1.append(" and area_code_district = ?                                         ");
            paramList.add(areaIdDistrict);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append(" and school_nick like ?                                             ");
            paramList.add(keyword);
        }
        Integer allPage = jdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb2 = new StringBuffer();
        sb2.append("  select                                                                ");
        sb2.append("    id,                                                                 ");
        sb2.append("    school_nick,                                                        ");
        sb2.append("    apply_count,                                                        ");
        sb2.append("    complete_count,                                                     ");
        sb2.append("    complete_rate                                                       ");
        sb2.append("  from yxy_continuedu.yxy_continuedu_statistics_school_task             ");
        sb2.append("  where task_id = ?                                                     ");
        if (StringUtils.isNotBlank(areaIdProvince)) {
            sb2.append(" and area_code_province = ?                                         ");
        }
        if (StringUtils.isNotBlank(areaIdCity)) {
            sb2.append(" and area_code_city = ?                                             ");
        }
        if (StringUtils.isNotBlank(areaIdDistrict)) {
            sb2.append(" and area_code_district = ?                                         ");
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append(" and school_nick like ?                                             ");
        }
        sb2.append(" limit ?,?                                      ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb2.toString(), paramList.toArray());
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("allPage", allPage);
        map.put("list", list);
        return map;
    }

    @Override
    public List<Map<String, Object>> renwuListAll(String schoolId, String keyword, String orderBy, String orderByStatus) {
        List<Object> paramList = new ArrayList<>();
        paramList.add(schoolId);
        StringBuffer sb2 = new StringBuffer();
        sb2.append("   select                                       ");
        sb2.append("     id,                                        ");
        sb2.append("     name,                                      ");
        sb2.append("     begin_date,                                 ");
        sb2.append("     end_date,                                   ");
        sb2.append("     apply_count,                                ");
        sb2.append("     complete_count,                             ");
        sb2.append("     complete_rate,                              ");
        sb2.append("     status                                     ");
        sb2.append("   from yxy_continuedu.yxy_continuedu_task      ");
        sb2.append("   where school_id = ?                          ");
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append(" and name like ?                            ");
        }
        if (StringUtils.isNotBlank(orderBy)) {
            if ("applyCount".equals(orderBy) && "up".equals(orderByStatus)) {
                sb2.append(" order by apply_count asc               ");
            }
            if ("applyCount".equals(orderBy) && "down".equals(orderByStatus)) {
                sb2.append(" order by apply_count desc              ");
            }
            if ("completeCount".equals(orderBy) && "up".equals(orderByStatus)) {
                sb2.append(" order by complete_count asc            ");
            }
            if ("completeCount".equals(orderBy) && "down".equals(orderByStatus)) {
                sb2.append(" order by complete_count desc           ");
            }
            if ("completeRate".equals(orderBy) && "up".equals(orderByStatus)) {
                sb2.append(" order by complete_rate asc             ");
            }
            if ("completeRate".equals(orderBy) && "down".equals(orderByStatus)) {
                sb2.append(" order by complete_rate desc            ");
            }
        } else {
            sb2.append(" order by begin_date desc, end_date desc, id asc    ");
        }
        return jdbcTemplate.queryForList(sb2.toString(), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> renwuDetailListAll(String taskId, String areaIdProvince, String areaIdCity, String areaIdDistrict, Integer bureauLevel, String keyword) {
        List<Object> paramList = new ArrayList<>();
        paramList.add(taskId);
        StringBuffer sb2 = new StringBuffer();
        sb2.append("  select                                                                ");
        sb2.append("    id,                                                                 ");
        sb2.append("    school_nick,                                                        ");
        sb2.append("    apply_count,                                                        ");
        sb2.append("    complete_count,                                                     ");
        sb2.append("    complete_rate                                                       ");
        sb2.append("  from yxy_continuedu.yxy_continuedu_statistics_school_task             ");
        sb2.append("  where task_id = ?                                                     ");

        if (StringUtils.isNotBlank(areaIdProvince)) {
            sb2.append(" and area_code_province = ?                                         ");
            paramList.add(areaIdProvince);
        }
        if (StringUtils.isNotBlank(areaIdCity)) {
            sb2.append(" and area_code_city = ?                                             ");
            paramList.add(areaIdCity);
        }
        if (StringUtils.isNotBlank(areaIdDistrict)) {
            sb2.append(" and area_code_district = ?                                         ");
            paramList.add(areaIdDistrict);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append(" and school_nick like ?                                             ");
            paramList.add(keyword);
        }
        return jdbcTemplate.queryForList(sb2.toString(), paramList.toArray());
    }
}
